package net.orange.console;

import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

/**
 * Jdbc console, which can run the query or update sql statement in the specify
 * database and export the result to the excel file, it usually control by the
 * developer or power users.
 */
public class JdbcConsole {
	/**
	 * The jdbc driver class name.
	 */
	private String jdbcDriver;
	/**
	 * The database connectionURL;
	 */
	private String databaseURL;
	/**
	 * The database userName.
	 */
	private String userName;
	/**
	 * The database password.
	 */
	private String passWord;

	/**
	 * The constructor with the jdbc driver name and connection URL.
	 * 
	 * @param jdbcDriver
	 *            The jdbc driver name.
	 * @param databaseURL
	 *            The connection URL.
	 * @param userName
	 *            The user name.
	 * @param passWord
	 *            The password.
	 */
	public JdbcConsole(String jdbcDriver, String databaseURL, String userName,
			String passWord) {
		this.jdbcDriver = jdbcDriver;
		this.databaseURL = databaseURL;
		this.userName = userName;
		this.passWord = passWord;		
	}

	/**
	 * @return the jdbcDriver
	 */
	public String getJdbcDriver() {
		return jdbcDriver;
	}

	/**
	 * @param jdbcDriver
	 *            the jdbcDriver to set
	 */
	public void setJdbcDriver(String jdbcDriver) {
		this.jdbcDriver = jdbcDriver;
	}

	/**
	 * @return the databaseURL
	 */
	public String getDatabaseURL() {
		return databaseURL;
	}

	/**
	 * @param databaseURL
	 *            the databaseURL to set
	 */
	public void setDatabaseURL(String databaseURL) {
		this.databaseURL = databaseURL;
	}

	/**
	 * @return the userName
	 */
	public String getUserName() {
		return userName;
	}

	/**
	 * @param userName
	 *            the userName to set
	 */
	public void setUserName(String userName) {
		this.userName = userName;
	}

	/**
	 * @return the passWord
	 */
	public String getPassWord() {
		return passWord;
	}

	/**
	 * @param passWord
	 *            the passWord to set
	 */
	public void setPassWord(String passWord) {
		this.passWord = passWord;
	}

	/**
	 * Execute the sql statement and return the result
	 * 
	 * @param sql
	 *            The sql statement.
	 * @return The result
	 * @throws Exception
	 */
	public CachedRowSet execute(String sql) throws Exception {
		Class.forName(jdbcDriver).newInstance();
		CachedRowSet rowSet = new CachedRowSetImpl();

		rowSet.setUrl(databaseURL);
		rowSet.setUsername(userName);
		rowSet.setPassword(passWord);

		rowSet.setCommand(sql);
		rowSet.execute();

		return rowSet;
	}

	/**
	 * Execute the sql statement and return the result.
	 * 
	 * @param sql
	 *            The sql statement.
	 * @param pagesize
	 *            How many rows will retrieve in one times.
	 * @return The result
	 * @throws Exception
	 */
	public CachedRowSet execute(String sql, int pagesize) throws Exception {

		Class.forName(jdbcDriver).newInstance();
		CachedRowSet rowSet = new CachedRowSetImpl();

		rowSet.setUrl(databaseURL);
		rowSet.setUsername(userName);
		rowSet.setPassword(passWord);

		rowSet.setCommand(sql);
		rowSet.setPageSize(pagesize);
		rowSet.execute();

		return rowSet;
	}
}
